﻿using iTool.SQL.Analysis.Context.Options;
using iTool.SQL.Analysis.Context.SQLWhereOptions;

using Microsoft.SqlServer.Management.SqlParser.SqlCodeDom;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace iTool.SQL.Analysis.Context
{
    public class SqlAnalysisContext
    {
        public SQLTableOptions Table { get; set; }
        public List<SQLFieldOptions> Fields { get; set; } = new List<SQLFieldOptions>();
        public SQLActionOptions SQLAction { get; set; }
        //public List<SqlQuerySpecification>? ChildrenQuerys { get; set; }
        public List<SqlSelectAnalysisContext>? ChildrenQuerys { get; set; }
        //public List<Type>? ChildrenQueryTypes { get; set; }
        public List<string> Errors { get; set; } = new List<string>();

        // public string WhereString { get; set; } = string.Empty;
        // base.context.WhereBuilder.ToString(base.context.WhereBuilder.Length - 4, 4).Equals("like")
        // public StringBuilder WhereBuilder { get; set; } = new StringBuilder();

        public List<GroupWhereOptions> GroupWheres { get; set; } = new List<GroupWhereOptions> { new GroupWhereOptions() };

        protected void SetConditions(string? schema, string table, bool isFirstQuery, List<GroupWhereOptions> groupWheres, SqlScriptOptions scriptOptions, string tag = "where")
        {
            // sub => group => where | sub
            if (groupWheres[0].Wheres.Any())
            {
                scriptOptions.ScriptContent.Append($" {tag} ");
                this.WhileConditions(schema, table, isFirstQuery, groupWheres, scriptOptions);
                this.AppendWhereTag(scriptOptions.ScriptContent, string.Empty);

                int index = 1;
                foreach (var item in tag)
                {
                    if (scriptOptions.ScriptContent[^index] != item)
                    {
                        index = 0;
                        break;
                    }
                    index++;
                }
                if (index > 0)
                {
                    int tagLength = tag.Length + 1;
                    scriptOptions.ScriptContent.Remove(scriptOptions.ScriptContent.Length - tagLength, tagLength);
                }
            }
        }
        protected virtual Dictionary<string, List<string>> GetTableFieldsMapDictionary(Dictionary<string, List<string>> tableFieldsMapDictionary = null)
        {
            return default;
        }
        private bool IsCanSetConditions(string? schema, string table, bool isFirstQuery, WhereOptions Where)
        {
            if (Where.Left is SQLFieldOptions && Where.Right is SQLFieldOptions)
            {
                return true;
            }

            object? oField = Where.Left is SQLFieldOptions ? Where.Left : (Where.Right is SQLFieldOptions ? Where.Right : null);
            if (oField != null)
            {
                SQLFieldOptions field = (SQLFieldOptions)oField;
                if (string.IsNullOrEmpty(field.Schema))
                {
                    if (!isFirstQuery)
                        return false;
                }
                else if (field.Schema != schema && field.Schema != table)
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
            return true;
        }
        
        private bool SetItemConditions(string? schema, string table, WhereOptions Where, SqlScriptOptions scriptOptions)
        {
            scriptOptions.DependenceFields = scriptOptions.DependenceFields ?? new List<SQLFieldOptions>();

            if (Where.Left is SQLFieldOptions lFieldOptions && Where.Right is SQLFieldOptions rFieldOptions)
            {
                if (!string.IsNullOrEmpty(lFieldOptions.Schema))
                {
                    if (lFieldOptions.Schema != schema && lFieldOptions.Schema != table)
                    {
                        scriptOptions.DependenceFields.Add(lFieldOptions);
                    }
                    else if (rFieldOptions.Schema != schema && rFieldOptions.Schema != table)
                    {
                        scriptOptions.DependenceFields.Add(rFieldOptions);
                    }
                }

                scriptOptions.ScriptContent.Append($"{lFieldOptions.Schema}.{lFieldOptions.Name}");
                scriptOptions.ScriptContent.Append(' ');
                scriptOptions.ScriptContent.Append(ComparisonBooleanExpressionTypeExtention.ToString(Where.ComparisonBooleanExpressionType));
                scriptOptions.ScriptContent.Append(' ');
                scriptOptions.ScriptContent.Append($"{rFieldOptions.Schema}.{rFieldOptions.Name}");
                return true;
            }
            else if (Where.Right is SubQueryOptions subQueryOptions)
            {
                scriptOptions.ChildrenQuerys.Add(new ChildQueryOptions
                {
                    ComparisonBooleanExpressionType = Where.ComparisonBooleanExpressionType,
                    Query = subQueryOptions.ToString()
                });
            }

            scriptOptions.ScriptContent.Append(Where.Left.ToString());
            scriptOptions.ScriptContent.Append(' ');
            scriptOptions.ScriptContent.Append(ComparisonBooleanExpressionTypeExtention.ToString(Where.ComparisonBooleanExpressionType));
            scriptOptions.ScriptContent.Append(' ');
            if (Where.Right is ValueOptions[] values)
            {
                scriptOptions.ScriptContent.Append('(');
                scriptOptions.ScriptContent.Append(string.Join(',', values.Select(item => item.ValueType == typeof(string) ? $"'{item.Value}'" : item.Value)));
                scriptOptions.ScriptContent.Append(')');
            }
            else
            {
                scriptOptions.ScriptContent.Append(Where.Right.ToString());
            }
            return true;
        }

        private void WhileConditions(string? schema, string table, bool isFirstQuery, IEnumerable<WhereBase> Wheres, SqlScriptOptions scriptOptions)
        {
            foreach (WhereBase whereBase in Wheres)
            {
                if (whereBase is SubWhereOptions subWhere)
                {
                    scriptOptions.ScriptContent.Append('(');
                    this.WhileConditions(schema, table, isFirstQuery, subWhere.Wheres, scriptOptions);
                    this.AppendWhereTag(scriptOptions.ScriptContent, string.Empty);
                    if (scriptOptions.ScriptContent[^1] == '(')
                    {
                        scriptOptions.ScriptContent.Remove(scriptOptions.ScriptContent.Length - 1, 1);
                    }
                    else
                    {
                        scriptOptions.ScriptContent.Append(')');
                    }
                }
                else if (whereBase is WhereOptions where)
                {
                    if (this.IsCanSetConditions(schema, table, isFirstQuery, where))
                    {
                        this.SetItemConditions(schema, table, where, scriptOptions);
                        this.AppendWhereTag(scriptOptions.ScriptContent, "and");
                    }
                }
                else if (whereBase is GroupWhereOptions group)
                {
                    this.WhileConditions(schema, table, isFirstQuery, group.Wheres, scriptOptions);
                    this.AppendWhereTag(scriptOptions.ScriptContent, "or");
                }
            }
        }

        private void AppendWhereTag(StringBuilder stringBuilder, string tag)
        {
            if (stringBuilder.Length > 4)
            {
                while (stringBuilder[^2] == ' ' && stringBuilder[^1] == ' ')
                {
                    stringBuilder.Remove(stringBuilder.Length - 1, 1);
                }

                // and 
                if (
                    stringBuilder[^2] == 'd' &&
                    stringBuilder[^3] == 'n' &&
                    stringBuilder[^4] == 'a'
                    )
                {
                    if (tag == "and") return;
                    else if (tag == string.Empty)
                    {
                        stringBuilder[^3] = ' ';
                        stringBuilder[^4] = ' ';
                        stringBuilder[^2] = ' ';
                    }
                    else if (tag == "or")
                    {
                        stringBuilder[^3] = 'r';
                        stringBuilder[^4] = 'o';
                        stringBuilder[^2] = ' ';
                        stringBuilder.Remove(stringBuilder.Length - 1, 1);
                        return;
                    }
                }

                if (
                    stringBuilder[^3] == 'o' &&
                    stringBuilder[^2] == 'r'
                    )
                {
                    if (tag == "or") return;
                    else if (tag == string.Empty)
                    {
                        stringBuilder[^3] = ' ';
                        stringBuilder[^2] = ' ';
                    }
                    else if (tag == "and")
                    {
                        stringBuilder[^3] = 'a';
                        stringBuilder[^2] = 'n';
                        stringBuilder[^1] = 'd';
                        stringBuilder.Append(' ');
                        return;
                    }
                }

                while (stringBuilder[^1] == ' ')
                {
                    stringBuilder.Remove(stringBuilder.Length - 1, 1);
                }

                if (tag != string.Empty)
                {
                    stringBuilder.Append(' ');
                    stringBuilder.Append(tag);
                    stringBuilder.Append(' ');
                }
            }
        }

    }
}
